package com.yangkunhong.dao;

import com.yangkunhong.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.sql.DataSource;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * ClassName: NewBaseDao
 * Description:
 *
 * @author yangkunhong
 * @date 2024/6/26 22:03
 */
public class NewBaseDao {
    protected Connection connection;
    public void startTransaction() {
        try {
            connection = DBUtils.getConnection();
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void commit() {
        try {
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public void rollback() {
        try {
            connection.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    private static DataSource getDataSource() {
        return DBUtils.getDataSource();
    }

    private static QueryRunner getQueryRunner() {
        return new QueryRunner(getDataSource());
    }

    /**
     * 插入数据到任意表，支持任意字段
     *
     * @param tableName 表名
     * @param columnValues 字段名和值的映射
     * @return 插入的自增主键值（如果表有自增主键）
     */
    public static Integer insertOne(String tableName, Map<String, Object> columnValues) {
        QueryRunner queryRunner = getQueryRunner();

        // 构建SQL语句和参数列表
        StringBuilder sql = new StringBuilder("INSERT INTO ").append(tableName).append("(");
        List<Object> params = new ArrayList<>();

        // 遍历字段名和值的映射，构建SQL语句和参数列表
        boolean isFirst = true;
        for (Map.Entry<String, Object> entry : columnValues.entrySet()) {
            if (!isFirst) {
                sql.append(",");
            }
            sql.append(entry.getKey());
            params.add(entry.getValue());
            isFirst = false;
        }

        sql.append(") VALUES(");
        for (int i = 0; i < columnValues.size(); i++) {
            if (i > 0) {
                sql.append(",");
            }
            sql.append("?");
        }
        sql.append(")");

        BigInteger insertId = BigInteger.ZERO;
        try {
            insertId = queryRunner.insert(sql.toString(), new ScalarHandler<>(), params.toArray());
        } catch (SQLException e) {
            System.out.println("插入失败");
            e.printStackTrace();
        }
        return insertId.intValue();
    }
    /**
     * 插入数据 获取自增主键的值
     * @param insertSql
     * @param params
     * @return
     */
    public static Integer  insertOne(String insertSql, Object... params){

        QueryRunner queryRunner = getQueryRunner();

        BigInteger insert = BigInteger.ZERO;
        try {
            insert = queryRunner.insert(insertSql, new ScalarHandler<>(), params);
        } catch (SQLException troubles) {
            System.out.println("插入失败");
            troubles.printStackTrace();
        }
        return insert.intValue();

    }
    /**
     * 插入数据 获取自增主键的值
     * @param updateSql 更新语句
     * @param params 参数
     * @return 受影响的行数
     */
    public static int  update(String updateSql, Object... params){

        QueryRunner queryRunner = getQueryRunner();

        int insert = 0;
        try {
            insert = queryRunner.update(updateSql, params);
        } catch (SQLException troubles) {
            System.out.println("更新失败");
            troubles.printStackTrace();
        }
        return insert;

    }
    public static int update(String tableName, Map<String, Object> columnValues, String whereClause, Object... whereParams) {
        QueryRunner queryRunner = getQueryRunner();

        // 构建SQL语句
        StringBuilder sql = new StringBuilder("UPDATE ").append(tableName).append(" SET ");
        boolean isFirst = true;
        for (Map.Entry<String, Object> entry : columnValues.entrySet()) {
            if (!isFirst) {
                sql.append(",");
            }
            sql.append(entry.getKey()).append("=").append("?");
            isFirst = false;
        }

        if (whereClause != null && !whereClause.isEmpty()) {
            sql.append(" WHERE ").append(whereClause);
        }

        // 合并参数
        List<Object> allParams = new ArrayList<>();
        allParams.addAll(columnValues.values());
        if (whereParams != null && whereParams.length > 0) {
            allParams.addAll(Arrays.asList(whereParams));
        }

        int rowsAffected = 0;
        try {
            System.out.println(sql);
            rowsAffected = queryRunner.update(sql.toString(), allParams.toArray());
        } catch (SQLException troubles) {
            System.out.println("更新失败");
            troubles.printStackTrace();
        }
        return rowsAffected;
    }
    /**
     * 插入数据 获取自增主键的值
     * @param deleteSql 更新语句
     * @param params 参数
     * @return 受影响的行数
     */
    public static int  delete(String deleteSql, Object... params){

        QueryRunner queryRunner = getQueryRunner();

        int insert = 0;
        try {
            insert = queryRunner.update(deleteSql, params);
        } catch (SQLException troubles) {
            System.out.println("删除失败");
            troubles.printStackTrace();
        }
        return insert;

    }
    public static int deleteSafe(String deleteSql, Object... params) {
        DataSource dataSource = getDataSource();
        QueryRunner queryRunner = new QueryRunner();
        int deletedRows = 0;

        try (Connection connection = dataSource.getConnection()) {
            connection.setAutoCommit(false);
            try {
                deletedRows = queryRunner.update(connection, deleteSql, params);
                connection.commit();
            } catch (SQLException e) {
                connection.rollback();

                // 你可以选择抛出异常或返回错误代码
                // throw new CustomException("删除失败", e);
            }
        } catch (SQLException e) {
             //LOGGER.error("获取数据库连接失败", e);
            // 你可以选择抛出异常或返回错误代码
            // throw new CustomException("获取数据库连接失败", e);
        }
        return deletedRows;
    }
    /**
     * 查询单条数据
     * @param clazz 实体类
     * @param sql 查询语句
     * @param params 参数
     * @param <T> 泛型
     * @return 返回实体类
     */
    public static <T>T getOne(Class<? extends T> clazz,String sql, Object... params){
        QueryRunner queryRunner = getQueryRunner();
        T query = null;
        try {
             query = queryRunner.query(sql, new BeanHandler<>(clazz), params);
        } catch (SQLException throwables) {
            System.out.println("查询失败");
            throwables.printStackTrace();
        }
        return query;
    }

    /**
     * 查询数据列表
     * @param clazz 实体类
     * @param sql 查询语句
     * @param params 参数
     * @param <T> 泛型
     * @return 返回List
     */
    public static  <T>List<T> getList(Class<? extends T> clazz,String sql, Object... params){
        QueryRunner queryRunner = getQueryRunner();
        List<T> query = new ArrayList<>();
        try {
            query = queryRunner.query(sql, new BeanListHandler<>(clazz), params);
        } catch (SQLException throwables) {
            System.out.println("查询失败");
            throwables.printStackTrace();
        }
        return query;
    }



}
